package com.sunwayworld.common.utils;

public class SQLOracleUtil {

	/**
	 * 生成分页的SQL语句
	 * 
	 * @param sqlStr
	 *            String
	 * @param cond
	 *            SplitPage
	 * @return String
	 */
	public static String getCorrectSqlStr(String sqlStr, SplitPage spage) {

		if (spage == null) {
			return sqlStr;
		}
		// 分页信息
		int numPerPage = spage.getNumPerPage();
		if (numPerPage <= 0)
			numPerPage = SplitPage.CONST_NUMPERPAGE;
		int currPage = spage.getCurrPage();
		if (currPage <= 1)
			currPage = SplitPage.CONST_CURRPAGE;

		return getCorrectSqlStr(sqlStr, currPage, numPerPage);

	}

	/**
	 * 生成分页的SQL语句
	 * 
	 * @param sqlStr
	 *            String
	 * @param currPage
	 *            int
	 * @param numPerPage
	 *            int
	 * @return String
	 */

	public static String getCorrectSqlStr(String sqlStr, int currPage,
			int numPerPage) {

		return "SELECT * FROM ( SELECT ROWNUM NEWROWNUM,a.* FROM (" + sqlStr
				+ ") a" + " ) WHERE NEWROWNUM<="
				+ Integer.toString(currPage * numPerPage) + " AND NEWROWNUM>="
				+ Integer.toString((currPage - 1) * numPerPage + 1);

	}

	/**
	 * 生成统计总数的SQL语句,已包含了行数 取数据是请按String[] 里的顺序 提取，最后一个为总行数
	 * 
	 * @param sqlStr
	 *            String 原SQL语句
	 * @param col
	 *            String[] 需要求和的 列名
	 * @author lim
	 * @since 2005-05-21
	 * @return String
	 */

	public static String getSumsSqlStr(String sqlStr, String[] cols) {
		return getSumsSqlStr(sqlStr, cols, true);
	}

	/**
	 * 生成统计总数的SQL语句,已包含了行数 取数据是请按String[] 里的顺序 提取，最后一个为总行数
	 * 
	 * @param sqlStr
	 *            String 原SQL语句
	 * @param col
	 *            String[] 需要求和的 列名
	 * @param flag
	 *            boolean true:(去掉 order by 后统计),false:(直接统计)
	 * @author lim
	 * @since 2005-05-21
	 * @return String
	 */
	public static String getSumsSqlStr(String sqlStr, String[] cols,
			boolean flag) {
		StringBuffer strbf = new StringBuffer();
		strbf.append("SELECT ");
		for (int i = 0; i < cols.length; i++) {
			strbf.append("SUM(NVL(");
			strbf.append(cols[i].trim());
			strbf.append(",0)),");
		}
		strbf.append(" COUNT(1) CNT FROM (");
		if (flag) {
			strbf.append(delOrderbyFromSQL(sqlStr));
		} else {
			strbf.append(sqlStr);
		}
		strbf.append(")");

		return strbf.toString();
	}
	
	
	  /**
     * 生成得到记录总数的SQL语句
     * @param sqlStr String
     * @return String
     */
    public static String getCountSqlStr(String sqlStr) {
        return getCountSqlStr(sqlStr, true);
    }

    /**
     * 生成得到记录总数的SQL语句
     * @param sqlStr String
     * @param flag boolean true:(去掉 order by 后统计),false:(直接统计)
     * @author lim
     * @since 2005-05-21
     * @return String
     */
    public static String getCountSqlStr(String sqlStr, boolean flag) {
        String reStr = null;
        if (flag) {
            reStr = "SELECT COUNT(1) CNT FROM (" + delOrderbyFromSQL(sqlStr) +
                    ")";
        } else {
            reStr = "SELECT COUNT(1) CNT FROM (" + sqlStr + ")";
        }
        return reStr;
    }

	/**
	 * 从SQL语句中删除排序
	 * 
	 * @param sqlStr
	 *            String
	 * @return String
	 * @author lim
	 * @since 2005-05-21
	 */
	private static String delOrderbyFromSQL(String sqlStr) {
		String reStr = null;
		String tmp = sqlStr.toLowerCase();
		int lastorder = tmp.lastIndexOf("order");
		int lasttmp = tmp.lastIndexOf(")");

		if (lastorder == -1) {
			reStr = sqlStr;
		} else {
			if (lasttmp == -1) {
				reStr = sqlStr.substring(0, lastorder);

			} else if (lastorder > lasttmp) {
				reStr = sqlStr.substring(0, lastorder);
			} else {
				reStr = sqlStr;
			}
		}
		return reStr;

	}
}
